/**
 * init.js
 * 数据库初始化文件 - PostgreSQL版本
 * 功能：初始化数据库表结构，统一表结构定义
 */

const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');

/**
 * 从 db_config.txt 读取数据库配置
 */
const loadDatabaseConfig = () => {
  try {
    const dbConfigPath = path.join(__dirname, '../db_config.txt');
    
    if (!fs.existsSync(dbConfigPath)) {
      console.warn('⚠️ 数据库配置文件不存在，使用默认配置');
      return {
        DB_USER: 'postgres',
        DB_HOST: 'localhost',
        DB_NAME: 'restaurant_db',
        DB_PASSWORD: 'postgres',
        DB_PORT: '5432'
      };
    }

    const configContent = fs.readFileSync(dbConfigPath, 'utf8').trim();
    const dbConfig = configContent.split('\n').reduce((config, line) => {
      const trimmedLine = line.trim();
      if (trimmedLine && !trimmedLine.startsWith('#')) {
        const [key, ...valueParts] = trimmedLine.split('=');
        const value = valueParts.join('=').trim();
        if (key && value) {
          config[key.trim()] = value;
        }
      }
      return config;
    }, {});

    console.log('✓ 数据库配置加载成功');
    return dbConfig;
  } catch (error) {
    console.error('× 加载数据库配置失败:', error.message);
    return {
      DB_USER: 'postgres',
      DB_HOST: 'localhost',
      DB_NAME: 'restaurant_db',
      DB_PASSWORD: 'postgres',
      DB_PORT: '5432'
    };
  }
};

const dbConfig = loadDatabaseConfig();

const pool = new Pool({
  user: dbConfig.DB_USER,
  host: dbConfig.DB_HOST,
  database: dbConfig.DB_NAME,
  password: dbConfig.DB_PASSWORD,
  port: parseInt(dbConfig.DB_PORT) || 5432,
});

/**
 * 统一表结构定义（与server.js保持一致）
 */
const initTables = async () => {
  try {
    console.log('开始初始化数据库表结构...');

    // 创建菜品表（统一使用SERIAL主键）
    await pool.query(`
      CREATE TABLE IF NOT EXISTS dishes (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        description TEXT,
        category VARCHAR(100),
        recipe TEXT,
        weather_condition VARCHAR(50) DEFAULT 'normal',
        image_url TEXT,
        is_active BOOLEAN DEFAULT TRUE,
        created_at BIGINT NOT NULL,
        updated_at BIGINT
      )
    `);
    console.log('✓ dishes 表初始化完成');

    // 创建用户表（统一使用SERIAL主键）
    await pool.query(`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        username VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role VARCHAR(50) NOT NULL,
        created_at BIGINT NOT NULL
      )
    `);
    console.log('✓ users 表初始化完成');

    // 创建订单表（统一使用SERIAL主键）
    await pool.query(`
      CREATE TABLE IF NOT EXISTS orders (
        id SERIAL PRIMARY KEY,
        order_id VARCHAR(255) UNIQUE NOT NULL,
        items JSONB NOT NULL,
        user_info JSONB NOT NULL,
        total_price DECIMAL(10, 2) NOT NULL,
        status VARCHAR(50) DEFAULT 'pending',
        created_at BIGINT NOT NULL,
        updated_at BIGINT,
        estimated_time VARCHAR(100)
      )
    `);
    console.log('✓ orders 表初始化完成');

    // 插入默认用户（如果不存在）
    const { rows: existingUsers } = await pool.query('SELECT COUNT(*) as count FROM users');
    if (parseInt(existingUsers[0].count) === 0) {
      await pool.query(`
        INSERT INTO users (username, password, role, created_at) VALUES
        ('女王', '20031013', 'customer', ${Date.now()}),
        ('御厨', '20030215', 'kitchen', ${Date.now()})
      `);
      console.log('✓ 默认用户数据初始化完成');
    }

    // 插入默认菜品（如果不存在）
    const { rows: existingDishes } = await pool.query('SELECT COUNT(*) as count FROM dishes');
    if (parseInt(existingDishes[0].count) === 0) {
      const defaultDishes = [
        {
          name: '宫保鸡丁',
          price: 38.00,
          description: '经典川菜，鸡肉鲜嫩，花生酥脆，酸甜微辣',
          category: '热菜',
          recipe: '1. 鸡肉切丁腌制\n2. 花生炸香备用\n3. 热锅爆炒调味\n4. 装盘上桌',
          weather_condition: 'normal',
          image_url: 'https://hpi-hub.tos-cn-beijing.volces.com/static/batch_14/1757354850592-5169.jpg',
          is_active: true,
          created_at: Date.now()
        },
        {
          name: '麻婆豆腐',
          price: 28.00,
          description: '经典川菜，豆腐嫩滑，麻辣鲜香',
          category: '热菜',
          recipe: '1. 豆腐切块焯水\n2. 肉末炒香\n3. 加入豆瓣酱调味\n4. 煮制入味',
          weather_condition: 'cold',
          image_url: 'https://hpi-hub.tos-cn-beijing.volces.com/static/batch_14/1757354850592-5169.jpg',
          is_active: true,
          created_at: Date.now()
        },
        {
          name: '糖醋排骨',
          price: 48.00,
          description: '酸甜可口，外酥里嫩，老少皆宜',
          category: '热菜',
          recipe: '1. 排骨切块腌制\n2. 油炸至金黄\n3. 糖醋汁收汁\n4. 撒芝麻装盘',
          weather_condition: 'normal',
          image_url: 'https://hpi-hub.tos-cn-beijing.volces.com/static/batch_14/1757354850592-5169.jpg',
          is_active: true,
          created_at: Date.now()
        },
        {
          name: '清炒时蔬',
          price: 18.00,
          description: '新鲜蔬菜，清淡爽口，健康营养',
          category: '素菜',
          recipe: '1. 蔬菜洗净切段\n2. 热锅快炒\n3. 简单调味\n4. 保持脆嫩',
          weather_condition: 'hot',
          image_url: 'https://hpi-hub.tos-cn-beijing.volces.com/static/batch_14/1757354850592-5169.jpg',
          is_active: true,
          created_at: Date.now()
        },
        {
          name: '酸辣汤',
          price: 15.00,
          description: '酸辣开胃，暖胃驱寒',
          category: '汤品',
          recipe: '1. 高汤煮沸\n2. 加入食材\n3. 调味勾芡\n4. 淋香油出锅',
          weather_condition: 'cold',
          image_url: 'https://hpi-hub.tos-cn-beijing.volces.com/static/batch_14/1757354850592-5169.jpg',
          is_active: true,
          created_at: Date.now()
        }
      ];

      for (const dish of defaultDishes) {
        await pool.query(`
          INSERT INTO dishes (name, price, description, category, recipe, weather_condition, image_url, is_active, created_at)
          VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
        `, [dish.name, dish.price, dish.description, dish.category, dish.recipe, dish.weather_condition, dish.image_url, dish.is_active, dish.created_at]);
      }
      console.log('✓ 默认菜品数据初始化完成');
    }

    console.log('='.repeat(60));
    console.log('数据库表结构初始化完成');
    console.log('='.repeat(60));
  } catch (error) {
    console.error('× 数据库表初始化失败:', error.message);
    console.warn('⚠️ 数据库初始化失败不会阻塞程序启动');
  }
};

/**
 * 测试数据库连接
 */
const testConnection = async () => {
  try {
    const result = await pool.query('SELECT NOW()');
    console.log('✓ 数据库连接测试成功:', result.rows[0].now);
    return true;
  } catch (error) {
    console.error('× 数据库连接测试失败:', error.message);
    return false;
  }
};

/**
 * 关闭数据库连接池
 */
const closePool = async () => {
  try {
    await pool.end();
    console.log('✓ 数据库连接池已关闭');
  } catch (error) {
    console.error('× 关闭数据库连接池失败:', error.message);
  }
};

module.exports = {
  pool,
  initTables,
  testConnection,
  closePool
};